"""
工时服务类
"""
from app.models import WorkHour, ProjectTask, Project
from app import db
from datetime import datetime, timedelta
from sqlalchemy import func


class WorkHourService:
    """工时服务类"""
    
    @staticmethod
    def get_deviation_stats(user_id):
        """获取工时偏差统计数据"""
        # 获取用户的任务工时统计
        task_stats = db.session.query(
            func.count(ProjectTask.id).label('total_tasks'),
            func.sum(ProjectTask.work_hour).label('total_estimated_hours'),
            func.sum(WorkHour.work_hours).label('total_actual_hours')
        ).join(WorkHour, ProjectTask.id == WorkHour.task_id).filter(
            ProjectTask.assignee_id == user_id
        ).first()
        
        total_tasks = task_stats.total_tasks or 0
        total_estimated = task_stats.total_estimated_hours or 0
        total_actual = task_stats.total_actual_hours or 0
        
        # 计算偏差任务数（实际工时超过估算工时20%以上）
        deviation_tasks = db.session.query(func.count(func.distinct(ProjectTask.id))).join(
            WorkHour, ProjectTask.id == WorkHour.task_id
        ).filter(
            ProjectTask.assignee_id == user_id,
            ProjectTask.work_hour > 0
        ).group_by(ProjectTask.id).having(
            (func.sum(WorkHour.work_hours) - ProjectTask.work_hour) / ProjectTask.work_hour > 0.2
        ).count() or 0
        
        # 计算正常任务数
        normal_tasks = total_tasks - deviation_tasks
        
        # 计算平均偏差率
        avg_deviation_rate = 0
        if total_estimated > 0:
            avg_deviation_rate = ((total_actual - total_estimated) / total_estimated) * 100
        
        return {
            'total_tasks': total_tasks,
            'normal_tasks': normal_tasks,
            'deviation_tasks': deviation_tasks,
            'avg_deviation_rate': avg_deviation_rate,
            'total_estimated_hours': total_estimated,
            'total_actual_hours': total_actual
        }
    
    @staticmethod
    def get_deviation_tasks(user_id):
        """获取偏差任务列表"""
        # 获取偏差任务列表
        deviation_tasks = db.session.query(
            ProjectTask.id,
            ProjectTask.name,
            ProjectTask.work_hour,
            Project.name.label('project_name'),
            func.sum(WorkHour.work_hours).label('actual_hours'),
            ((func.sum(WorkHour.work_hours) - ProjectTask.work_hour) / ProjectTask.work_hour * 100).label('deviation_rate'),
            ProjectTask.status
        ).join(WorkHour, ProjectTask.id == WorkHour.task_id).join(
            Project, ProjectTask.project_id == Project.id
        ).filter(
            ProjectTask.assignee_id == user_id,
            ProjectTask.work_hour > 0
        ).group_by(ProjectTask.id, ProjectTask.name, ProjectTask.work_hour, Project.name, 
                  ProjectTask.status).having(
            (func.sum(WorkHour.work_hours) - ProjectTask.work_hour) / ProjectTask.work_hour > 0.2
        ).all()
        
        # 获取用户信息
        from app.models import User
        user = User.query.get(user_id)
        user_name = user.name if user else '未知用户'
        
        # 转换结果为字典列表
        result = []
        for task in deviation_tasks:
            result.append({
                'id': task.id,
                'name': task.name,
                'project_name': task.project_name,
                'estimated_hours': task.work_hour,
                'actual_hours': task.actual_hours or 0,
                'deviation_rate': task.deviation_rate or 0,
                'deviation_reason': '待分析',
                'deviation_solution': '',
                'deviation_date': '',
                'status': task.status,
                'assignee_name': user_name
            })
        
        return result